package it.disim.tlp.webreputation.business.impl;

import it.disim.tlp.webreputation.business.AbstractDataAggregatorFactory;
import it.disim.tlp.webreputation.business.FactoryBuilder;
import it.disim.tlp.webreputation.business.PostService;
import it.disim.tlp.webreputation.business.TopicService;
import it.disim.tlp.webreputation.business.model.Post;
import it.disim.tlp.webreputation.business.model.Sorgente;
import it.disim.tlp.webreputation.business.model.Topic;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


public class JDBCPostService implements PostService{
	
	/** 
	 * The method saves the object of type Post () in the DB by entering Topics in turn if they are not in the DB.
	 * Then performs a check on the existence or not of the Topics that are inside the object passed as Collection Post.
	 * @param post An object of type Post () "filled" (with the list of Topics inside ().)
	 * @return int Returns an integer that represents the id of the post just inserted into the database.
	 */
	@Override
	public int savePost(Post post) {
		
		Connection con = new DBConnect().getConnection();
		
		int result = -1;
				
		PreparedStatement st = null;
		try {
			
			st = con.prepareStatement("INSERT INTO post (Link, Data, Polarity, ID_Fonte, Text) VALUES (?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
			st.setString(1, post.getLink());
			
			//Data
			//The Post class maintains the date as java.util.Date, while maintains the date PreparedStatement with java.sql.Date
			//then we have to do is change the date format "util" in "sql" and pass it to the PreparedStatement.		
			java.util.Date utilDate = post.getData();
			java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
			
			
			st.setDate(2, sqlDate);
			st.setString(3, post.getPolarity());
			st.setInt(4, post.getSorgente().getId());
			st.setString(5, post.getText());
			st.executeUpdate();		
			
			//We take the key you just generated by the insertion
			ResultSet keys_post = st.getGeneratedKeys();  				  
			keys_post.next();  
			int id_post_inserito = keys_post.getInt(1);  
			keys_post.close(); 		
				
			result = id_post_inserito;
			
			AbstractDataAggregatorFactory factory = FactoryBuilder.getInstance();
			TopicService service = factory.getTopicService();
			
			for(Topic t: post.getTopics()){
				if(!service.checkIfExist(t)){	
					
					//We insertion directly into the JDBC Topic Topic
					int id_topic_inserito = service.saveTopic(t);										
					
					PreparedStatement st_contenuto = null;
					st_contenuto = con.prepareStatement("INSERT INTO contenuto (IDPost, IDTopic) VALUES (?,?)");
					st_contenuto.setInt(1, id_post_inserito);
					st_contenuto.setInt(2, id_topic_inserito);	
					st_contenuto.executeUpdate();
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (st!=null) {
				try {st.close();} catch (SQLException e) {}
			}			

		}
		
		return result;
		
	}

	/**
	 * The method fetches all the posts in the database.
	 * @return List<Post> Returns a list of all the posts of DB.
	 */
	@Override
	public List<Post> getAllPosts() {
		
		Connection con = new DBConnect().getConnection();
		
		Statement st = null;
		ResultSet rs = null;
		List<Post> result = new ArrayList<Post>();
		try {
		
			st = con.createStatement();			
			rs = st.executeQuery("SELECT * FROM post");
			while (rs.next()) {

				int id = rs.getInt("ID");
				String link = rs.getString("Link");
				Date data = rs.getDate("Data");
				String polarity = rs.getString("polarity");
				
				Sorgente sorgente = new Sorgente();		
				int id_fonte = rs.getInt("ID_Fonte");
				sorgente.setId(id_fonte);
				
				String text = rs.getString("Text");
				
				result.add(new Post(id, link, data, polarity, text, sorgente, null));
			}
			

		} catch (SQLException e) {
			e.printStackTrace();			
		} finally {
			if (st!=null) {
				try {st.close();} catch (SQLException e) {}
			}

		}
		return result;
	}
	
	
}
